package fun.codefarmer.pm.utils;

import fun.codefarmer.pm.model.*;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @ @ClassName POIUtils
 * @ Descriotion TODO
 * @ Author admin
 * @ Date 2020/3/28 15:27
 **/
public class POIUtils {
    public static ResponseEntity<byte[]> employee2Excel(List<Employee> list) {
        //床架Excel 文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建文档摘要
        workbook.createInformationProperties();
        //获取文档要信息
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
        //文档类别
        docInfo.setCategory("员工信息");
        //文档管理员
        docInfo.setManager("codefarmer");
        //设置公司
        docInfo.setCompany("公司");
        //获取文档摘要信息
        SummaryInformation summInfo = workbook.getSummaryInformation();
        //文档标题
        summInfo.setTitle("员工信息表");
        //文档作者
        summInfo.setAuthor("作者codefarmer");
        //文档备注
        summInfo.setComments("文档有codefarmer提供");
        //创建样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        // 设置背景颜色
        headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
        //创建表单
        HSSFSheet sheet = workbook.createSheet("员工信息表");
        sheet.setColumnWidth(0,5*256);
        sheet.setColumnWidth(1,5*256);
        sheet.setColumnWidth(2,5*256);
        sheet.setColumnWidth(3,5*256);
        sheet.setColumnWidth(4,12*256);
        sheet.setColumnWidth(5,20*256);
        sheet.setColumnWidth(6,10*256);
        sheet.setColumnWidth(7,10*256);
        sheet.setColumnWidth(8,16*256);
        sheet.setColumnWidth(9,12*256);
        sheet.setColumnWidth(10,15*256);
        sheet.setColumnWidth(11,20*256);
        sheet.setColumnWidth(12,16*256);
        sheet.setColumnWidth(13,14*256);
        sheet.setColumnWidth(14,12*256);
        sheet.setColumnWidth(15,8*256);
        sheet.setColumnWidth(16,16*256);
        sheet.setColumnWidth(17,20*256);
        sheet.setColumnWidth(18,20*256);
        sheet.setColumnWidth(19,20*256);
        sheet.setColumnWidth(20,20*256);
        sheet.setColumnWidth(21,5*256);
        sheet.setColumnWidth(22,10*256);
        sheet.setColumnWidth(23,20*256);
        sheet.setColumnWidth(24,20*256);
        //创建标题行
        HSSFRow row0 = sheet.createRow(0);
        HSSFCell cell0 = row0.createCell(0);
        cell0.setCellValue("编号");
        cell0.setCellStyle(headerStyle);
        HSSFCell cell1 = row0.createCell(1);
        cell1.setCellValue("姓名");
        cell1.setCellStyle(headerStyle);
        HSSFCell cell2 = row0.createCell(2);
        cell2.setCellValue("工号");
        cell2.setCellStyle(headerStyle);
        HSSFCell cell3 = row0.createCell(3);
        cell3.setCellValue("性别");
        cell3.setCellStyle(headerStyle);
        HSSFCell cell4 = row0.createCell(4);
        cell4.setCellValue("出生日期");
        cell4.setCellStyle(headerStyle);
        HSSFCell cell5 = row0.createCell(5);
        cell5.setCellValue("身份证号");
        cell5.setCellStyle(headerStyle);
        HSSFCell cell6 = row0.createCell(6);
        cell6.setCellValue("婚姻状况");
        cell6.setCellStyle(headerStyle);
        HSSFCell cell7 = row0.createCell(7);
        cell7.setCellValue("民族");
        cell7.setCellStyle(headerStyle);
        HSSFCell cell8 = row0.createCell(8);
        cell8.setCellValue("籍贯");
        cell8.setCellStyle(headerStyle);
        HSSFCell cell9 = row0.createCell(9);
        cell9.setCellValue("政治面貌");
        cell9.setCellStyle(headerStyle);
        HSSFCell cell10 = row0.createCell(10);
        cell10.setCellValue("电子邮件");
        cell10.setCellStyle(headerStyle);
        HSSFCell cell11 = row0.createCell(11);
        cell11.setCellValue("电话号码");
        cell11.setCellStyle(headerStyle);
        HSSFCell cell12 = row0.createCell(12);
        cell12.setCellValue("联系地址");
        cell12.setCellStyle(headerStyle);
        HSSFCell cell13 = row0.createCell(13);
        cell13.setCellValue("所属部门");
        cell13.setCellStyle(headerStyle);
        HSSFCell cell14 = row0.createCell(14);
        cell14.setCellValue("职称");
        cell14.setCellStyle(headerStyle);
        HSSFCell cell15 = row0.createCell(15);
        cell15.setCellValue("职位");
        cell15.setCellStyle(headerStyle);
        HSSFCell cell16 = row0.createCell(16);
        cell16.setCellValue("应聘形式");
        cell16.setCellStyle(headerStyle);
        HSSFCell cell17 = row0.createCell(17);
        cell17.setCellValue("入职时间");
        cell17.setCellStyle(headerStyle);
        HSSFCell cell18 = row0.createCell(18);
        cell18.setCellValue("转正日期");
        cell18.setCellStyle(headerStyle);
        HSSFCell cell19 = row0.createCell(19);
        cell19.setCellValue("合同起始日期");
        cell19.setCellStyle(headerStyle);
        HSSFCell cell20 = row0.createCell(20);
        cell20.setCellValue("合同终止日期");
        cell20.setCellStyle(headerStyle);
        HSSFCell cell21 = row0.createCell(21);
        cell21.setCellValue("合同期限（年）");
        cell21.setCellStyle(headerStyle);
        HSSFCell cell22 = row0.createCell(22);
        cell22.setCellValue("最高学历");
        cell22.setCellStyle(headerStyle);
        HSSFCell cell23 = row0.createCell(23);
        cell23.setCellValue("专业");
        cell23.setCellStyle(headerStyle);
        HSSFCell cell24 = row0.createCell(24);
        cell24.setCellValue("毕业院校");
        cell24.setCellStyle(headerStyle);

        for (int i = 0; i < list.size(); i++) {
            Employee employee = list.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(i+1+"");
            row.createCell(1).setCellValue(employee.getName());
            row.createCell(2).setCellValue(employee.getWorkid());
            row.createCell(3).setCellValue(employee.getGender());
            HSSFCell cell_4 = row.createCell(4);
            cell_4.setCellStyle(dateCellStyle);
            cell_4.setCellValue(employee.getBirthday());

            row.createCell(5).setCellValue(employee.getIdcard());
            row.createCell(6).setCellValue(employee.getWedlock());
            row.createCell(7).setCellValue(employee.getNation().getName());
            row.createCell(8).setCellValue(employee.getNativeplace());
            row.createCell(9).setCellValue(employee.getPoliticsstatus().getName());
            row.createCell(10).setCellValue(employee.getEmail());
            row.createCell(11).setCellValue(employee.getPhone());
            row.createCell(12).setCellValue(employee.getAddress());
            row.createCell(13).setCellValue(employee.getDepartment().getName());
            row.createCell(14).setCellValue(employee.getJobLevel().getName());
            System.out.println("名字="+employee.getName()+";;;i="+i+";;;JobLevel="+employee.getJobLevel());
            row.createCell(15).setCellValue(employee.getPosition().getName());
            row.createCell(16).setCellValue(employee.getEngageform());
            HSSFCell cell_17 = row.createCell(17);
            cell_17.setCellStyle(dateCellStyle);
            cell_17.setCellValue(employee.getBegindate());
            HSSFCell cell_18 = row.createCell(18);
            cell_18.setCellStyle(dateCellStyle);
            cell_18.setCellValue(employee.getConversiontime());
            HSSFCell cell_19 = row.createCell(19);
            cell_19.setCellStyle(dateCellStyle);
            cell_19.setCellValue(employee.getBegincontract());
            HSSFCell cell_20 = row.createCell(20);
            cell_20.setCellStyle(dateCellStyle);
            cell_20.setCellValue(employee.getEndcontract());
            row.createCell(21).setCellValue(employee.getContractterm());
            row.createCell(22).setCellValue(employee.getTiptopdegree());
            row.createCell(23).setCellValue(employee.getSpecialty());
            row.createCell(24).setCellValue(employee.getSchool());
        }
        //以流的形式转出
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        //
        HttpHeaders header = new HttpHeaders();
        try {
            //无论那种方法都需设置头，                                防止乱码
            header.setContentDispositionFormData("attachment",new String("员工表.xls".getBytes("UTF-8"),"ISO-8859-1"));
            header.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //需要转成byte[]，头，http状态码

        return new ResponseEntity<byte[]>(baos.toByteArray(),header, HttpStatus.CREATED);
    }


    /**
     * excel 解析成员工集合
     * @param file
     * @param allNation 民族
     * @param allPoliticsstatus 政治面貌
     * @param allPosition 职位
     * @param allDepartment 部门
     * @param jobLevel 职称
     * @return
     */
    public static List<Employee> excel2Employee(MultipartFile file, List<Nation> allNation, List<PoliticsStatus> allPoliticsstatus, List<Position> allPosition, List<DepartMent> allDepartment, List<JobLevel> jobLevel) {
        List<Employee> list = new ArrayList<>();
        Employee employee = null;
        try {
            //1 创建 workbook 对象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            //2 获取 workbook 中表单数量 sheet页
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i <numberOfSheets ; i++) {
                // 获取表单
                HSSFSheet sheetAt = workbook.getSheetAt(i);
                // 获取表单行数
                int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
                for (int j = 0; j <physicalNumberOfRows ; j++) {
                    //第一行标题行，不需要解析
                    if (j == 0) {
                        continue;
                    }
                    HSSFRow row = sheetAt.getRow(j);
                    //判断是否有空行，空行不解析
                    if (row == null) {
                        continue;
                    }
                    //获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    employee = new Employee();
                    for (int k = 0; k <physicalNumberOfCells ; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            case STRING:
                                String cellValue = cell.getStringCellValue();
                                switch (k) {
                                    case 1:
                                        employee.setName(cellValue);
                                        break;
                                    case 2:
                                        employee.setWorkid(cellValue);
                                        break;
                                    case 3:
                                        employee.setGender(cellValue);
                                        break;
                                    case 5:
                                        employee.setIdcard(cellValue);
                                        break;
                                    case 6:
                                        employee.setWedlock(cellValue);
                                        break;
                                    case 7:
                                        //Nation 中修改hashcode方法
                                        // indexOf方法 是 hashCode 和 equals 方法，需要id和name 重写hash 方法只让它比较name
                                        int nationIndex = allNation.indexOf(new Nation(cellValue));
                                        employee.setNationid(allNation.get(nationIndex).getId());
                                        break;
                                    case 8:
                                        employee.setNativeplace(cellValue);
                                        break;
                                    case 9:
                                        //通过for 循环查找政治面貌id
                                        employee.setPoliticid(getId(allPoliticsstatus,cellValue));
                                        break;
                                    case 10:
                                        employee.setEmail(cellValue);
                                        break;
                                    case 11:
                                        employee.setPhone(cellValue);
                                        break;
                                    case 12:
                                        employee.setAddress(cellValue);
                                        break;
                                    case 13:
                                        //部门
                                        employee.setDepartmentid(getDeparmentId(allDepartment,cellValue));
                                        break;
                                        //职称
                                    case 14:
                                        employee.setJoblevelid(getJobLevelId(jobLevel,cellValue));
                                        break;
                                    case 15:
                                        //职位
                                        employee.setPosid(getPostionId(allPosition,cellValue));
                                        break;
                                    case 16:
                                        employee.setEngageform(cellValue);
                                        break;
                                    /*case 21:
                                        employee.setContractTerm(Double.parseDouble(cellValue));
                                        break;*/
                                    case 22:
                                        employee.setTiptopdegree(cellValue);
                                        break;
                                    case 23:
                                        employee.setSpecialty(cellValue);
                                        break;
                                    case 24:
                                        employee.setSchool(cellValue);
                                        break;
                                }
                                break;
                            default:{
                                switch (k) {
                                    case 4:
                                        employee.setBirthday(cell.getDateCellValue());
                                        break;
                                    case 17:
                                        employee.setBegindate(cell.getDateCellValue());
                                        break;
                                    case 18:
                                        employee.setConversiontime(cell.getDateCellValue());
                                        break;
                                    case 19:
                                        employee.setBegincontract(cell.getDateCellValue());
                                        break;
                                    case 20:
                                        employee.setEndcontract(cell.getDateCellValue());
                                        break;
                                    case 21:
                                        employee.setContractterm(cell.getNumericCellValue());
                                        break;
                                }
                            }
                            break;
                        }
                    }
                    list.add(employee);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 获取职称 id 存入员工表中
     * @param jobLevel
     * @param cellValue
     * @return
     */
    private static Integer getJobLevelId(List<JobLevel> jobLevel, String cellValue) {
        Integer jobLevelId = 0;
        for (int i = 0; i < jobLevel.size(); i++) {
            String jobLevelName = jobLevel.get(i).getName();
            if (jobLevelName.equals(cellValue)) {
                jobLevelId = jobLevel.get(i).getId();
            }
        }
        return jobLevelId;
    }

    /**
     * 职位
     * @param allPosition
     * @param cellValue
     * @return
     */
    private static Integer getPostionId(List<Position> allPosition, String cellValue) {
        Integer posid = 0;
        for (int i = 0; i < allPosition.size(); i++) {
            String name1 = allPosition.get(i).getName();
            if (cellValue.equals(name1)) {
                posid = allPosition.get(i).getId();
            }
        }
        return posid;
    }

    /**
     * 获得部门 id  填错时 部门id会返回0
     * @param allDepartment
     * @param cellValue
     * @return
     */
    private static Integer getDeparmentId(List<DepartMent> allDepartment, String cellValue) {
        Integer id = 0;
        for (int i = 0; i <allDepartment.size() ; i++) {
            String name1 = allDepartment.get(i).getName();
            if (cellValue.equals(name1)) {
                id = allDepartment.get(i).getId();
            }
        }
        return id;
    }

    /**
     * 获取政治面貌id
     * @param list
     * @param name
     * @return
     */
    public static int getId(List<PoliticsStatus> list, String name) {
        int id = 0;
        for (int i = 0; i < list.size(); i++) {
            String name1 = list.get(i).getName();
            if (name.equals(name1)) {
                id = list.get(i).getId();
            }
        }
        return id;
    }
}
